package SqlJavaBean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class TeacherR {
	private Connection con;
	
	public TeacherR(Connection c){
		con = c;
		init();
	}
	private void init(){
		try{
			Statement sta = con.createStatement();
			sta.execute("use websql");
		}
		catch(Exception e){
			
		}
	}
	
	public String SeePart(){
		String sql = "select * from Part";
		return runSql(sql);
	}
	public String SeeExam(){
		String sql = "select * from Exam";
		return runSql(sql);
	}
	public String SeePartStu(String partName){
		String sql = "select partName,Practise.user,name,sum(grade),avg(ranks) ";
		sql = sql+" from Practise,User ";
		sql = sql + " where Practise.user=User.user and partName='"+partName+"'";
		sql = sql +" group by partName,user,name";
		return runSql(sql);
	}
	public String SeeExamStu(String examName){
		String sql = "select Subject.examName,Subject.user,name,sum(grade) ";
		sql = sql+" from Subject,User ";
		sql = sql + " where Subject.user=User.user and examName='"+examName+"'";
		sql = sql +" group by Subject.examName,user,name";
		return runSql(sql);
	}
	public String SeeStuExam(String user,String examName){  //获取学生某考试的试题详细
		String sql = "select Subject.subjectID,data,anserData,grade from Subject,ExamDataBank ";
		sql = sql+" where Subject.subjectID=ExamDataBank.subjectID and user='"+user+"'";
		return runSql(sql);
	}
	public String SeeStuPart(String user,String partName){ //获取学生某练习的试题详细
		String sql = "select Practise.practiseID,data,anserData,whenLong,grade,ranks";
		sql = sql+" from Practise,PartDataBank ";
		sql = sql+" where Practise.practiseID=PartDataBank.practiseID and user='"+user+"'";
		sql = sql+" and Practise.partName='"+partName+"'";
		return runSql(sql);
	}
//*******************************************************************************************************	
	
	private String runSql(String sql){
		String res = "";
		try{
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(sql);
			return getResult(rs);
		}
		catch(Exception e){
			res = res + e.getMessage();
		}
		return "";
	}
	private String getResult(ResultSet rs){
		String res = "";
		try{
			int count = rs.getMetaData().getColumnCount();
			while(rs.next()){
				for(int i=1;i<=count;i++) res = res+rs.getString(i)+"\u0002";
				res = res+"\u0001";
			}
			return res;
		}
		catch(Exception e){
			res = res+e.getMessage();
		}
		
		return "";
	}
	public static void main(String[] xxx){
		DBBean db = new DBBean();
		Connection con  = db.getConnection();
		TeacherR t = new TeacherR(con);
		System.out.println(t.SeeStuPart("test","xxxtest"));
	}
}
